var express = require('express');
var router = express.Router();

const Query = require('../utils/db.js')
let { api_url } = require('../utils/config.js')




/* GET home page. */
router.get('/', function (req, res, next) {
  res.send('列表', { title: 'Express123' });
});


//地区
router.get('/areas', async function (req, res, next) {
  let sql = `SELECT * FROM e_area`

  let { err, result } = await Query(sql)

  if (err) {
    next({
      msg: '稍后再试',
      status: 500
    })
    return
  }

  let data = {
    msg: 'OK',
    status: 200,
    result: result
  }
  res.send(data)
});


//年级
router.get('/grades', async function (req, res, next) {
  let sql = `SELECT * FROM e_grade`

  let { err, result } = await Query(sql)

  if (err) {
    next({
      msg: '稍后再试',
      status: 500
    })
    return
  }

  let data = {
    msg: 'OK',
    status: 200,
    result: result
  }
  res.send(data)
})


//学科
router.get('/subjects', async function (req, res, next) {
  let sql = `SELECT * FROM e_subject`

  let { err, result } = await Query(sql)

  if (err) {
    next({
      msg: '稍后再试',
      status: 500
    })
    return
  }

  let data = {
    msg: 'OK',
    status: 200,
    result: result
  }
  res.send(data)
})


//类别选择
router.get('/cates', async function (req, res, next) {
  let sql = `SELECT * FROM e_category`

  let { err, result } = await Query(sql)

  if (err) {
    next({
      msg: '稍后再试',
      status: 500
    })
    return
  }

  let data = {
    msg: 'OK',
    status: 200,
    result: result
  }
  res.send(data)
})


//课程列表
router.get('/courses', async function (req, res, next) {


  //0 接受所有的参数
  let { page = 1, size = 5, area = '', subject = '',grades='',category='' } = req.query

  //1 分页原理
  //  let page=1  //如果未传递，则默认第一页
  //  let size=5 //取5条

  //将page数字转为字符串
  page = page + ''

  //出现小数情况时：
  //如果page的数为小数时，则报错。
  if (page.indexOf('.') != -1) {
    next({
      msg: '请传递一个整数的页码',
      status: 500
    })
    return
  }

  //将size数字转为字符串
  size = size + ''

  //如果size的数为小数时，则报错。
  if (size.indexOf('.') != -1) {
    next({
      msg: '请传递一个整数的条数',
      status: 500
    })
    return
  }

  //没出现小数情况时：
  //将page和size由字符串转为数字
  page = Number(page)
  size = Number(size)

  let startIndex = (page - 1) * size //从第几条开始


  // 2 处理筛选条件
  let whereStr = ''     //存放筛选条件的变量字符串，如果为空，则没有条件；不为空，则有条件，就用AND连接

  //  let area='北京'
  //  let subject='语文'

  //如果area不为空时，则查找以area的值为筛选条件
  if (area != '') {
    if (whereStr == '') {
      whereStr += ` WHERE area_name='${area}' `
    } else {
      whereStr += ` AND area_name='${area} `
    }
  }


  if (subject != '') {
    if (whereStr == '') {
      whereStr += ` WHERE subject_name='${subject}' `
    } else {
      whereStr += ` AND subject_name='${subject}' `
    }

  }

  if (grades != '') {
    if (whereStr == '') {
      whereStr += ` WHERE grade_name='${grades}' `
    } else {
      whereStr += ` AND grade_name='${grades}' `
    }

  }


  if (category != '') {
    if (whereStr == '') {
      whereStr += ` WHERE category_name='${category}' `
    } else {
      whereStr += ` AND category_name='${category}' `
    }

  }




  let spl1 = `SELECT id,cid,type_name,CONCAT('${api_url}/uploads/courses/',image_src) AS image_src,subject_name,title,area_name,price,type FROM e_course ${whereStr} ORDER BY id DESC LIMIT ${startIndex},${size}`

  //获取总条数sql2
  let sql2 = `SELECT * FROM e_course ${whereStr}`


  // 3 执行sql语句
  let res1 = await Query(spl1)
  let res2 = await Query(sql2)
  // res.send(res2)

  // return

  // 4 根据业务封装返回的data数据
  let rows = res2.result.length   //总条数

  let totalPage = Math.ceil(rows / size) //总页码

  let data = {
    page: page,
    totalPage: totalPage,
    rows: rows,
    data: res1.result
  }

  res.send(data)
})



//搜索
router.get('/search', async function (req, res, next) {
  let { pageSize = 5, keyword = '', page = 1 } = req.query


  if (keyword == '') {
    next({
      "msg": "请传递必要参数",
      "status": 500
    })
    return
  }

  let whereStr = ''
  if (keyword != '') {
    if (whereStr == '') {
      whereStr += ` WHERE (title LIKE '%${keyword}%') or (price LIKE '%${keyword}%') or (type_name LIKE '%${keyword}%') or (type_name LIKE '%${keyword}%') or (area_name LIKE '%${keyword}%') or (grade_name LIKE '%${keyword}%') or (subject_name LIKE '%${keyword}%') `
    } else {
      whereStr += ` AND (title LIKE '%${keyword}%') or (price LIKE '%${keyword}%') or (type_name LIKE '%${keyword}%') or (type_name LIKE '%${keyword}%') or (area_name LIKE '%${keyword}%') or (grade_name LIKE '%${keyword}%') or (subject_name LIKE '%${keyword}%')  `
    }
  }

  page = Number(page)
  pageSize = Number(pageSize)
  let startIndex = (page - 1) * pageSize 
 

  let spl1 = `SELECT id,cid,title,price,type_name,CONCAT('${api_url}/uploads/courses/',image_src) AS image_src,intro,detail_imgs,type,type_name,area_name,grade_name,subject_name,category_name,tid,status FROM e_course ${whereStr} ORDER BY id DESC LIMIT ${startIndex},${pageSize}`

  let sql2 = `SELECT * FROM e_course ${whereStr} `//总条数

  let res1 = await Query(spl1)
  // res.send(res1)
  let res2 = await Query(sql2)
  // res.send(res1)
  // res.send(res2.result.length+'')

  let rows = res2.result.length+''   //总条数
  

  let totalPage = Math.ceil(rows / pageSize)+'' //总页码


  let data={
    msg: 'OK',
    status:200,
    result:{
      page,
      totalPage,
      rows,
      keyword,
      data:res1.result
    }
  
  }
  res.send(data)

})






module.exports = router;
